DROP TABLE CustomEntryList_Contents;
DROP TABLE CustomEntryLists;
DROP TABLE AutoEntryList_OptionFilter;
DROP TABLE AutoEntryList_EntryFilter;
DROP TABLE AutoEntryLists;
DROP TABLE EntryLists;
DROP TABLE KeyValueOptionFilter;
DROP TABLE OptionFilter;
DROP TABLE EntryType_EntryFilter;
DROP TABLE EntryTypes;
DROP TABLE EntryFilter_Icon;
DROP TABLE MultiEntryFilter_EntryFilter;
DROP TABLE MultiEntryFilter;
DROP TABLE KeyValueEntryFilter;
DROP TABLE RootEntryFilter;
DROP TABLE EntryFilter;
DROP TABLE EntryProperties_Single;
DROP TABLE EntryProperties_Guid;
DROP TABLE EntryProperties_DateTime;
DROP TABLE EntryProperties_Bool;
DROP TABLE EntryProperties_String;
DROP TABLE EntryProperties_Int64;
DROP TABLE EntryProperties_Int32;
DROP TABLE EntryProperties;
DROP TABLE Properties;
DROP TABLE FileEntries;
DROP TABLE FolderEntries;
DROP TABLE Entries;
DROP TABLE VolumePermissions;
DROP TABLE Users;
DROP TABLE SecondaryVolumes;
DROP TABLE PrimaryVolumes;
DROP TABLE Volumes;


-- Volumes
CREATE TABLE Volumes (
    VolumeId nvarchar(20) NOT NULL,
    FactoryKey nvarchar(20) NOT NULL,
    Parameters nvarchar(255) NOT NULL,
	AccessMode int NOT NULL,
    CONSTRAINT PK_Volumes PRIMARY KEY (VolumeId)
); 
CREATE TABLE PrimaryVolumes (
    VolumeId nvarchar(20) NOT NULL,
    CONSTRAINT PK_PrimaryVolumes PRIMARY KEY (VolumeId),
    CONSTRAINT FK_PrimaryVolume_Inherit_Volume FOREIGN KEY(VolumeId)
    REFERENCES Volumes(VolumeId) ON DELETE CASCADE    
);
CREATE TABLE SecondaryVolumes (
    VolumeId nvarchar(20) NOT NULL,
    CONSTRAINT PK_SecondaryVolumes PRIMARY KEY (VolumeId),
    CONSTRAINT FK_SecondaryVolume_Inherit_Volume FOREIGN KEY(VolumeId) 
    REFERENCES Volumes(VolumeId) ON DELETE CASCADE    
);

INSERT INTO Volumes(VolumeId, FactoryKey, Parameters, AccessMode)
  VALUES ('COFE_PROFILEROOT', 'ProfileRoot', '', 0);
INSERT INTO PrimaryVolumes(VolumeId) VALUES ('COFE_PROFILEROOT');
INSERT INTO Volumes(VolumeId, FactoryKey, Parameters, AccessMode)
  VALUES ('', 'ProfileRoot', '', 0);
INSERT INTO SecondaryVolumes(VolumeId) VALUES ('');

-- Users, VolumePermissions
CREATE TABLE Users (
    UserId nvarchar(20) NOT NULL,
    UserSecret nvarchar(20) NOT NULL,
    UserEmail nvarchar(40),
    UserQuestion nvarchar(40),
    UserAnswer nvarchar(20),
    CreationTime datetime NOT NULL,
    LoginTime datetime  NOT NULL,
    AuthorizeKey uniqueidentifier  NOT NULL,
    CONSTRAINT PK_Users PRIMARY KEY (UserId)
);

CREATE TABLE VolumePermissions (
    UserId nvarchar(20)  NOT NULL,
    VolumeId nvarchar(20)  NOT NULL,
    CONSTRAINT PK_Permissions PRIMARY KEY (UserId, VolumeId),
    CONSTRAINT FK_Permission_User FOREIGN KEY (UserId) REFERENCES Users(UserId) ON DELETE CASCADE,
    CONSTRAINT FK_Permission_Volume FOREIGN KEY (VolumeId) REFERENCES Volumes(VolumeId) ON DELETE CASCADE
);

-- Entries, File and FolderEntries
CREATE TABLE Entries (
    EntryId uniqueidentifier DEFAULT (x'00000000000000000000000000000000'),
    ParentId uniqueidentifier  NOT NULL,
    PrimaryVolumeId nvarchar(20)  NOT NULL,
    SecondaryVolumeId nvarchar(20),
    ParsePath nvarchar(512)  NOT NULL,
    Label nvarchar(100)  NOT NULL,
    Name nvarchar(100)  NOT NULL,    
    RefreshTime datetime  NOT NULL,
    PropertyRefreshTime datetime  NOT NULL,
    SupportedAttributes nvarchar(512)  NOT NULL,
    WritableAttributes nvarchar(512)  NOT NULL,
    FileAttributes int  NOT NULL,
    COFEAttributes int  NOT NULL,
    CreationTimeUtc datetime  NOT NULL,
    LastWriteTimeUtc datetime  NOT NULL,
    LastAccessTimeUtc datetime  NOT NULL,
    IsFileSystem bit  NOT NULL,
    
    CONSTRAINT PK_Entries PRIMARY KEY (EntryId),
    --CONSTRAINT UK_EntriesPath UNIQUE (PrimaryVolumeId, ParsePath),
    CONSTRAINT FK_Entry_PrimaryVolume FOREIGN KEY (PrimaryVolumeId) 
        REFERENCES PrimaryVolumes(VolumeId) ON DELETE CASCADE,	
    CONSTRAINT FK_Entry_SecondaryVolume FOREIGN KEY (SecondaryVolumeId) 
        REFERENCES SecondaryVolumes(VolumeId),
    CONSTRAINT FK_Entry_ParentEntry FOREIGN KEY (ParentId) 
        REFERENCES Entries(EntryId) ON DELETE CASCADE --Sqlite uses ON DELETE CASCADE
);

-- Insert the initial ProfileRoot entry, then add the constraint of ParentEntry required.
PRAGMA foreign_keys = OFF;
INSERT INTO Entries(EntryId, ParentId, 
PrimaryVolumeId, ParsePath, Label, Name, 
RefreshTime, PropertyRefreshTime, 
SupportedAttributes, WritableAttributes, FileAttributes, COFEAttributes, 
CreationTimeUtc, LastWriteTimeUtc, LastAccessTimeUtc, IsFileSystem)
VALUES (
(x'00000000000000000000000000000000'),(x'00000000000000000000000000000000'),
'COFE_PROFILEROOT', '','', '', 
datetime(),datetime(),
0,0,0,0,
datetime(),datetime(),datetime(),0);
PRAGMA foreign_keys = ON;

CREATE TABLE FileEntries (
    EntryId uniqueidentifier NOT NULL,
    Length bigint NOT NULL,
    CRC nvarchar(32) NOT NULL,
    CONSTRAINT PK_FileEntries PRIMARY KEY (EntryId),
	  CONSTRAINT FK_FileEntry_Entry FOREIGN KEY (EntryId) REFERENCES Entries(EntryId) ON DELETE CASCADE
);

CREATE TABLE FolderEntries (
    EntryId uniqueidentifier NOT NULL,
	LastListTime datetime NOT NULL,
	IsFileBasedFS bit NOT NULL,
	DirectoryType int NOT NULL,
	SubFolderCount int NOT NULL,
	FileCount int NOT NULL,
  CONSTRAINT PK_FolderEntries PRIMARY KEY (EntryId),
	CONSTRAINT FK_FolderEntry_Entry FOREIGN KEY (EntryId) REFERENCES Entries(EntryId) ON DELETE CASCADE
);

-- Properties
CREATE TABLE Properties ( 
    PropertyId int NOT NULL,
    FullName nvarchar(256)  NOT NULL,
    ResourceName nvarchar(256)  NULL,
    IsMetadata bit  NOT NULL,
    IsResource bit  NOT NULL,
    DataType nvarchar(20)  NOT NULL,
    CONSTRAINT PK_Properties PRIMARY KEY (PropertyId)
);

CREATE TABLE EntryProperties (
    EntryId uniqueidentifier  NOT NULL,
    PropertyId int  NOT NULL,    
    CONSTRAINT PK_EntryProperties PRIMARY KEY (EntryId, PropertyId),
    CONSTRAINT FK_EntryProperty_Property FOREIGN KEY (PropertyId) REFERENCES Properties(PropertyId) ON DELETE CASCADE,
    CONSTRAINT FK_EntryProperty_Entry FOREIGN KEY (EntryId) REFERENCES Entries(EntryId)ON DELETE CASCADE
);

CREATE TABLE EntryProperties_Int32 (
    Value int  NOT NULL,
    EntryId uniqueidentifier  NOT NULL,
    PropertyId int  NOT NULL,	
    CONSTRAINT PK_EntryProperties_Int32 PRIMARY KEY (EntryId, PropertyId),
    CONSTRAINT FK_EntryPropertyInt32_inherits_EntryProperty FOREIGN KEY (EntryId, PropertyId)
      REFERENCES EntryProperties(EntryId, PropertyId) ON DELETE CASCADE
);


CREATE TABLE EntryProperties_Int64 (
    Value bigint  NOT NULL,
    EntryId uniqueidentifier  NOT NULL,
    PropertyId int  NOT NULL,	
    CONSTRAINT PK_EntryProperties_Int64 PRIMARY KEY (EntryId, PropertyId),
    CONSTRAINT FK_EntryPropertyInt64_inherits_EntryProperty FOREIGN KEY (EntryId, PropertyId)
      REFERENCES EntryProperties(EntryId, PropertyId) ON DELETE CASCADE
);

CREATE TABLE EntryProperties_String (
    Value nvarchar(2000)  NOT NULL,
    EntryId uniqueidentifier  NOT NULL,
    PropertyId int  NOT NULL,
    CONSTRAINT PK_EntryProperties_String PRIMARY KEY (EntryId, PropertyId),
    CONSTRAINT FK_EntryPropertyString_inherits_EntryProperty FOREIGN KEY (EntryId, PropertyId)
      REFERENCES EntryProperties(EntryId, PropertyId) ON DELETE CASCADE
);

CREATE TABLE EntryProperties_Bool (
    Value bit  NOT NULL,
    EntryId uniqueidentifier  NOT NULL,
    PropertyId int  NOT NULL,
    CONSTRAINT PK_EntryProperties_Bool PRIMARY KEY (EntryId, PropertyId),
    CONSTRAINT FK_EntryPropertyBool_inherits_EntryProperty FOREIGN KEY (EntryId, PropertyId)
      REFERENCES EntryProperties(EntryId, PropertyId) ON DELETE CASCADE
);

CREATE TABLE EntryProperties_DateTime (
    Value dateTime NOT NULL,
    EntryId uniqueidentifier  NOT NULL,
    PropertyId int  NOT NULL,
    CONSTRAINT PK_EntryProperties_DateTime PRIMARY KEY (EntryId, PropertyId),
    CONSTRAINT FK_EntryPropertyDateTime_inherits_EntryProperty FOREIGN KEY (EntryId, PropertyId)
      REFERENCES EntryProperties(EntryId, PropertyId) ON DELETE CASCADE
);

CREATE TABLE EntryProperties_Guid (
    Value uniqueidentifier NOT NULL,
    EntryId uniqueidentifier  NOT NULL,
    PropertyId int  NOT NULL,
    CONSTRAINT PK_EntryProperties_Guid PRIMARY KEY (EntryId, PropertyId),
    CONSTRAINT FK_EntryPropertyGuid_inherits_EntryProperty FOREIGN KEY (EntryId, PropertyId)
      REFERENCES EntryProperties(EntryId, PropertyId) ON DELETE CASCADE
);

CREATE TABLE EntryProperties_Single (
    Value real NOT NULL,
    EntryId uniqueidentifier  NOT NULL,
    PropertyId int  NOT NULL,
    CONSTRAINT PK_EntryProperties_Single PRIMARY KEY (EntryId, PropertyId),
    CONSTRAINT FK_EntryPropertySingle_inherits_EntryProperty FOREIGN KEY (EntryId, PropertyId)
      REFERENCES EntryProperties(EntryId, PropertyId) ON DELETE CASCADE
);

-- EntryFilter 
CREATE TABLE EntryFilter (
    EntryFilterId uniqueidentifier NOT NULL,    
	CONSTRAINT PK_EntryFilter PRIMARY KEY (EntryFilterId)
);

CREATE TABLE RootEntryFilter (
    EntryFilterId uniqueidentifier NOT NULL,
    Negative bit NOT NULL,
    EntryId uniqueidentifier NOT NULL,
	--Dummy int,
	CONSTRAINT PK_RootEntryFilter PRIMARY KEY (EntryFilterId),
	CONSTRAINT FK_RootEntryFilter_inherits_EntryFilter FOREIGN KEY (EntryFilterId)
       REFERENCES EntryFilter(EntryFilterId) ON DELETE CASCADE,
    CONSTRAINT FK_RootEntryFilter_Entry FOREIGN KEY (EntryId) REFERENCES Entries(EntryId) ON DELETE CASCADE
);

CREATE TABLE KeyValueEntryFilter(
    EntryFilterId uniqueidentifier NOT NULL,
    EntryFilterKey varchar(20) NOT NULL,	
	Value varchar(250) NOT NULL,
	CONSTRAINT PK_KeyValueEntryFilter PRIMARY KEY (EntryFilterId),
    CONSTRAINT UK_KeyValueEntryFilter UNIQUE (EntryFilterKey, Value),
	CONSTRAINT FK_KeyValueEntryFilter_inherits_EntryFilter FOREIGN KEY (EntryFilterId)
       REFERENCES EntryFilter(EntryFilterId) ON DELETE CASCADE
);

CREATE TABLE MultiEntryFilter (
    EntryFilterId uniqueidentifier NOT NULL,
	Type varchar(20) NOT NULL,
	CONSTRAINT PK_MultiEntryFilter PRIMARY KEY (EntryFilterId),
	CONSTRAINT FK_MultiEntryFilter_inherits_EntryFilter FOREIGN KEY (EntryFilterId)
       REFERENCES EntryFilter(EntryFilterId) ON DELETE CASCADE
);

CREATE TABLE MultiEntryFilter_EntryFilter (
    EntryFilterId uniqueidentifier NOT NULL,
	ChildEntryFilterId uniqueidentifier NOT NULL,
	Position int NOT NULL,
	CONSTRAINT PK_MultiEntryFilter_EntryFilter PRIMARY KEY (EntryFilterId, ChildEntryFilterId),    
	CONSTRAINT UK_MultiEntryFilter_Position UNIQUE (EntryFilterId, Position),
	CONSTRAINT FK_MultiEntryFilter_EntryFilter FOREIGN KEY (EntryFilterId)
       REFERENCES MultiEntryFilter(EntryFilterId) ON DELETE CASCADE,
  CONSTRAINT FK_MultiEntryFilter_EntryFilter_Child FOREIGN KEY (ChildEntryFilterId)
      REFERENCES EntryFilter(EntryFilterId) ON DELETE CASCADE
);

CREATE TABLE EntryFilter_Icon (
	EntryFilterId uniqueidentifier NOT NULL,
	Size int NOT NULL,
	Icon blob NOT NULL,
	CONSTRAINT PK_EntryFilter_Icon PRIMARY KEY (EntryFilterId, Size),
	CONSTRAINT FK_EntryFilter_Icon_EntryFilter FOREIGN KEY (EntryFilterId)
	    REFERENCES EntryFilter(EntryFilterId) ON DELETE CASCADE
);

-- EntryType
CREATE TABLE EntryTypes (
    EntryTypeId uniqueidentifier NOT NULL,    
    Description nvarchar(50) NOT NULL,
    ContentType nvarchar(50) ,
    CONSTRAINT PK_EntryTypes PRIMARY KEY (EntryTypeId)
);

CREATE TABLE EntryType_EntryFilter (
    EntryTypeId uniqueidentifier NOT NULL,
	EntryFilterId uniqueidentifier NOT NULL,
	CONSTRAINT PK_EntryType_EntryFilter PRIMARY KEY (EntryTypeId, EntryFilterId),
	CONSTRAINT FK_EntryType_EntryFilter_EntryType FOREIGN KEY (EntryTypeId)
	    REFERENCES EntryTypes(EntryTypeId) ON DELETE CASCADE,
	CONSTRAINT FK_EntryType_EntryFilter_EntryFilter FOREIGN KEY (EntryFilterId)
	    REFERENCES EntryFilters(EntryFilterId) ON DELETE CASCADE
);

-- OptionFilter
CREATE TABLE OptionFilter (
    OptionFilterId uniqueidentifier NOT NULL,
	CONSTRAINT PK_OptionFilter PRIMARY KEY (OptionFilterId)
);

CREATE TABLE KeyValueOptionFilter(
    OptionFilterId uniqueidentifier NOT NULL,
    OptionFilterKey varchar(20) NOT NULL,	
	Value varchar(250) NOT NULL,
	CONSTRAINT PK_KeyValueOptionFilter PRIMARY KEY (OptionFilterId),
	CONSTRAINT UK_KeyValueOptionFilter UNIQUE (OptionFilterKey, Value),	
    CONSTRAINT FK_KeyValueOptionFilter_inherits_OptionFilter FOREIGN KEY (OptionFilterId)
       REFERENCES OptionFilter(OptionFilterId) ON DELETE CASCADE
);

-- EntryList
CREATE TABLE EntryLists (
    EntryListId uniqueidentifier NOT NULL,
    OwnerId nvarchar(20)  NOT NULL,    
    Name nvarchar(20) NOT NULL,
    LastUpdateTime datetime NOT NULL,
    CONSTRAINT UK_EntryListName UNIQUE (OwnerId, Name),
    CONSTRAINT PK_EntryLists PRIMARY KEY (EntryListId),
    CONSTRAINT FK_EntryList_User FOREIGN KEY (OwnerId) 
	    REFERENCES Users(UserId) ON DELETE CASCADE
);

CREATE TABLE AutoEntryLists (
    EntryListId uniqueidentifier NOT NULL,
    Status int DEFAULT 0,
    CONSTRAINT PK_AutoEntryLists PRIMARY KEY (EntryListId),
    CONSTRAINT FK_AutoEntryList_inherits_EntryList FOREIGN KEY (EntryListId) 
       REFERENCES EntryLists(EntryListId) ON DELETE CASCADE
);

CREATE TABLE AutoEntryList_EntryFilter (
    EntryListId uniqueidentifier NOT NULL,
	EntryFilterId uniqueidentifier NOT NULL,
	CONSTRAINT PK_AutoEntryList_EntryFilter PRIMARY KEY (EntryListId, EntryFilterId),
	CONSTRAINT FK_AutoEntryList_EntryFilter_AutoEntryList FOREIGN KEY (EntryListId)
	   REFERENCES AutoEntryLists(EntryListId) ON DELETE CASCADE,
	CONSTRAINT FK_AutoEntryList_EntryFilter_EntryFilter FOREIGN KEY (EntryFilterId)
	   REFERENCES EntryFilters(EntryFilterId) ON DELETE CASCADE
);

CREATE TABLE AutoEntryList_OptionFilter (
    EntryListId uniqueidentifier NOT NULL,
	OptionFilterId uniqueidentifier NOT NULL,
	CONSTRAINT PK_AutoEntryList_OptionFilter PRIMARY KEY (EntryListId, OptionFilterId),
	CONSTRAINT FK_AutoEntryList_OptionFilter_AutoEntryList FOREIGN KEY (EntryListId)
	   REFERENCES AutoEntryLists(EntryListId) ON DELETE CASCADE,
	CONSTRAINT FK_AutoEntryList_OptionFilter_OptionFilter FOREIGN KEY (OptionFilterId)
	   REFERENCES OptionFilters(OptionFilterId) ON DELETE CASCADE
);

CREATE TABLE CustomEntryLists (
    EntryListId uniqueidentifier NOT NULL,    
    CONSTRAINT PK_CustomEntryList PRIMARY KEY (EntryListId),
    CONSTRAINT FK_CustomEntryList_inherits_EntryList FOREIGN KEY (EntryListId) 
       REFERENCES EntryLists(EntryListId) ON DELETE CASCADE
);

CREATE TABLE CustomEntryList_Contents (
    EntryListId uniqueidentifier NOT NULL,
    EntryId uniqueidentifier  NOT NULL,
	Position int,
    CONSTRAINT PK_CustomEntryList_Contents PRIMARY KEY (EntryListId,EntryId),
    CONSTRAINT FK_CustomEntryList_Contents_Entry FOREIGN KEY (EntryId) REFERENCES Entries(EntryId) ON DELETE CASCADE,
    CONSTRAINT FK_CustomEntryList_Contents_EntryList FOREIGN KEY (EntryListId) 
       REFERENCES CustomEntryLists(EntryListId) ON DELETE CASCADE
);
